﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using CFM.DTO;
using CFM.DAL;

namespace CFM.API
{
    public class FormulaProcessAPI
    {
        private DataHelper _helper = null;
        public FormulaProcessAPI()
        {
            _helper = new DataHelper();
        }
        public List<FormulaProcess> GettFormulaProcessByProductId(string foproID)
        {
            List<FormulaProcess> listfopro = new List<FormulaProcess>();
            string sql = "select b.ProcessProductId , A.ProductName,b.Quantity,c.UnitId, C.UnitName from (Product A join FormulaProcess B on A.ProductId=B.ProcessProductId)  join Unit C on C.UnitId=B.UnitId where B.ProductId = @ProductId";
            SqlParameter[] prs = 
            {
                new SqlParameter("@ProductId",foproID),
              //  new SqlParameter("@UnitId",foproID)
            };
            SqlDataReader dr = _helper.ExcuteDataReader(sql, prs);
            return _helper.MapDataReaderToObject<FormulaProcess>(dr);
        }
        public bool Create(FormulaProcess formulaProcess)
        {           
            string sql = "if not exists  (select * from FormulaProcess where ProductId = @ProductId and ProcessProductId = @ProcessProductId) "
                        + " insert into FormulaProcess(ProductId,UnitId,ProcessProductId,Quantity) values(@ProductId,@UnitId,@ProcessProductId,@Quantity)"
                    + " else"
                    + " begin"
                        + " declare @qt int"
                        + " set @qt = (select Quantity from FormulaProcess where ProductId = @ProductId and ProcessProductId = @ProcessProductId ) + @Quantity;"
                        + " update FormulaProcess set Quantity = (@qt),UnitId=@UnitId  where  ProductId = @ProductId and ProcessProductId = @ProcessProductId;	"
                    + " end;";
            string sqlUpdateBuyprice = " update Product set BuyPrice ="
                     + " ("
                        + " select SUM(a.Quantity * b.BuyPrice * b.Rate) from"
                        + " FormulaProcess a, Product b"
                        + " where a.ProcessProductId = b.ProductId"
                        + " and  a.ProductId = @ProductId "
                        + " group by a.ProductId"
                     + " )"
                      + " where ProductId = @ProductId";
            sql += sqlUpdateBuyprice;
            SqlParameter[] prs = 
            {
                new SqlParameter("@ProductId",formulaProcess.ProductId),
                new SqlParameter("@ProcessProductId",formulaProcess.ProcessProductId),
                new SqlParameter("@Quantity",formulaProcess.Quantity),
                new SqlParameter("@UnitId",formulaProcess.UnitId),
            };
            return _helper.ExcuteNonQuery(sql, prs);
        }
        public bool Update(FormulaProcess formulaProcess)
        {
            string sql =  "update FormulaProcess set ProductId=@ProductId,ProcessProductId=@ProcessProductId,Quantity=@Quantity,UnitId=@UnitId where ProductId = @ProductId and ProcessProductId = @ProcessProductId";
            string sqlUpdateBuyprice = " update Product set BuyPrice ="
                     + " ("
                        + " select SUM(a.Quantity * b.BuyPrice * b.Rate) from"
                        + " FormulaProcess a, Product b"
                        + " where a.ProcessProductId = b.ProductId"
                        + " and  a.ProductId = @ProductId "
                        + " group by a.ProductId"
                     + " )"
                      + " where ProductId = @ProductId";
            sql += sqlUpdateBuyprice;
            SqlParameter[] prs = 
            {
                new SqlParameter("@ProductId",formulaProcess.ProductId),               
                new SqlParameter("@ProcessProductId",formulaProcess.ProcessProductId),
                new SqlParameter("@Quantity",formulaProcess.Quantity),
                new SqlParameter("@UnitId",formulaProcess.UnitId),
            };
            return _helper.ExcuteNonQuery(sql, prs);
        }
        public bool Delete(string productId, string formulaProcessId)
        {
            string sql = "delete FormulaProcess where ProductId = @ProductId and ProcessProductId =@ProcessProductId";
            string sqlUpdateBuyprice = " update Product set BuyPrice ="
                     + " ("
                        + " select SUM(a.Quantity * b.BuyPrice * b.Rate) from"
                        + " FormulaProcess a, Product b"
                        + " where a.ProcessProductId = b.ProductId"
                        + " and  a.ProductId = @ProductId "
                        + " group by a.ProductId"
                     + " )"
                      + " where ProductId = @ProductId";
            sql += sqlUpdateBuyprice;
            SqlParameter[] prs = 
            {
                new SqlParameter("@Productid",productId),
                new SqlParameter("@ProcessProductId",formulaProcessId),
            };
            return _helper.ExcuteNonQuery(sql, prs);
        }
    }
}
